package org.cloudfoundry.samples;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.inject.Inject;
import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.cloudfoundry.samples.domain.CustomerLocation;
import org.cloudfoundry.samples.domain.MobileInfo;
import org.cloudfoundry.samples.domain.State;
import org.cloudfoundry.samples.domain.TaxiLocation;
import org.cloudfoundry.samples.domain.UserReg;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class ReferenceDataRepository {
	
    private static final Logger logger = LoggerFactory.getLogger(ReferenceDataRepository.class);
    
	private JdbcTemplate jdbcTemplate;
	
	@Inject
	public void init(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public String getDbInfo() {
		DataSource dataSource = jdbcTemplate.getDataSource();
		if (dataSource instanceof BasicDataSource) {
			return ((BasicDataSource) dataSource).getUrl();
		}
		else if (dataSource instanceof SimpleDriverDataSource) {
			return ((SimpleDriverDataSource) dataSource).getUrl();
		}
		return dataSource.toString();
	}
	
	public List<State> findAll() {
		return this.jdbcTemplate.query("select * from current_states", new RowMapper<State>() {
				public State mapRow(ResultSet rs, int rowNum) throws SQLException {
					State s = new State();
					s.setId(rs.getLong("id"));
					s.setStateCode(rs.getString("state_code"));
					s.setName(rs.getString("name"));
					return s;
				}
			});
	}

	public List<MobileInfo> getMobiles() {
		return this.jdbcTemplate.query("select * from mobile_info", new RowMapper<MobileInfo>() {
			public MobileInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
				MobileInfo s = new MobileInfo();
				s.setId(rs.getLong("id"));
				s.setMobileNumber(rs.getString("mobile_number"));
				s.setDeviceId(rs.getString("device_id"));
				return s;
			}
		});
	}

	public void saveMobileInfo(final MobileInfo message) {
		String sql = "insert into mobile_info(mobile_number, device_id) values(?, ?)";
		this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, message.getMobileNumber());
				ps.setString(2, message.getDeviceId());
			}
		});
	}

    public List<CustomerLocation> getPassengerLocations(int min) {
        logger.info("getPassengerLocations...");
        
        String sql = "select * from customer_location where create_time >= DATE_SUB(NOW(),INTERVAL " + min + " MINUTE)" ;
        return this.jdbcTemplate.query(sql, new RowMapper<CustomerLocation>() {
            public CustomerLocation mapRow(ResultSet rs, int rowNum) throws SQLException {
                CustomerLocation s = new CustomerLocation();
                s.setUserId(rs.getString("customer_id"));
                s.setPhoneNumber(rs.getString("phone_number"));
                s.setAddress(rs.getString("address"));
                s.setLon(rs.getString("lon"));
                s.setLat(rs.getString("lat"));
                s.setCreateTime(rs.getDate("create_time"));
                return s;
            }
        });
    }

    public List<TaxiLocation> getTaxiLocations(int min) {
        logger.info("getTaxiLocations...");
        
        String sql = "select * from taxi_location where create_time >= DATE_SUB(NOW(),INTERVAL " + min + " MINUTE)" ;
        
        return this.jdbcTemplate.query(sql, new RowMapper<TaxiLocation>() {
            public TaxiLocation mapRow(ResultSet rs, int rowNum) throws SQLException {
                TaxiLocation s = new TaxiLocation();
                s.setUserId(rs.getString("taxi_id"));
                s.setPhoneNumber(rs.getString("phone_number"));
                s.setAddress(rs.getString("address"));
                s.setLon(rs.getString("lon"));
                s.setLat(rs.getString("lat"));
                s.setCreateTime(rs.getDate("create_time"));
                return s;
            }
        });
    }

    public void savePassengerLocation(final CustomerLocation location) {
        logger.info("saveCustomerLocation:" + location.toString());
        
        String sql = "insert into customer_location(customer_id, lat, lon, phone_number, address, create_time, update_time) " +
                "values(?, ?, ?, ?, ?, ?, ?)";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, location.getUserId());
                ps.setString(2, location.getLat());
                ps.setString(3, location.getLon());
                ps.setString(4, location.getPhoneNumber());
                ps.setString(5, location.getAddress());
                ps.setDate(6, new java.sql.Date(location.getCreateTime().getTime()));
                ps.setDate(7, new java.sql.Date(location.getUpdateTime().getTime()));
            }
        });
    }

    public void saveTaxiLocation(final TaxiLocation location) {
        logger.info("saveTaxiLocation:" + location.toString());
        
        String sql = "insert into taxi_location(taxi_id, lat, lon, phone_number, address, create_time, update_time) " +
                "values(?, ?, ?, ?, ?, ?, ?)";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, location.getUserId());
                ps.setString(2, location.getLat());
                ps.setString(3, location.getLon());
                ps.setString(4, location.getPhoneNumber());
                ps.setString(5, location.getAddress());
                ps.setDate(6, new java.sql.Date(location.getCreateTime().getTime()));
                ps.setDate(7, new java.sql.Date(location.getUpdateTime().getTime()));
            }
        });
    }

    public int getCustomerType(String deviceid) {
        logger.info("getCustomerType:" + deviceid);
        
        String sql = "select * from user_reg where deviceid = " + deviceid;
        
        List<UserReg> userList = this.jdbcTemplate.query(sql, new RowMapper<UserReg>() {
            public UserReg mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserReg s = new UserReg();
                s.setUserType(rs.getString("user_type"));
                return s;
            }
        });
        
        if(userList != null && userList.size() > 0){
            return Integer.valueOf(userList.get(0).getUserType());
        }
        
        return 10;
    }
    
    public UserReg listRegUser(String deviceid) {
        logger.info("listRegUser:" + deviceid);
        
        String sql = "select * from user_reg where deviceid = " + deviceid;
        
        List<UserReg> userList = this.jdbcTemplate.query(sql, new RowMapper<UserReg>() {
            public UserReg mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserReg s = new UserReg();
                s.setId(rs.getLong("id"));
                s.setUserType(rs.getString("user_type"));
                return s;
            }
        });
        
        if(userList != null && userList.size() > 0){
            return userList.get(0);
        }
        
        return null;
    }

}
